<?php

namespace app\common;
use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Style\Alignment;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

class MyExcel
{

    public function exportTemplateExcel($data)
    {

        $name = date("Y-m-d", time()).rand(1,1000);
        $spreadsheet = new Spreadsheet();

        /*foreach ($data as $key => $item) {
            $this->opSheet($spreadsheet,$key,$item);
        }*/

        $objActSheet = $spreadsheet->setActiveSheetIndex(0);//设置当前的活动sheet

        $sheet = $spreadsheet->getActiveSheet(0)->setTitle("sheet1");
        $i = 1;
        $spreadsheet->getActiveSheet(0)->getStyle('A')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet(0)->getStyle('B')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet(0)->getStyle('C')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet(0)->getStyle('D')->getFont()->setSize(18);
        $spreadsheet->getActiveSheet(0)->getStyle('E')->getFont()->setSize(18);
        foreach ($data as $channel=>$channelItem){

            foreach ($channelItem as $trade=>$tradeItem){

                foreach ($tradeItem as $career=>$careerItem){

                    foreach ($careerItem as $item){
                        $spreadsheet->getActiveSheet(0)->setCellValue("A".$i, $channel);
                        $spreadsheet->getActiveSheet(0)->setCellValue("B".$i, $trade);
                        $spreadsheet->getActiveSheet(0)->setCellValue("C".$i, $career);
                        $spreadsheet->getActiveSheet(0)->setCellValue("D".$i, $item['report']);
                        $spreadsheet->getActiveSheet(0)->setCellValue("E".$i, $item['month']);
                        $i++;
                    }


                }
            }

        }


        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $name . '.xlsx"');
        header('Cache-Control: max-age=0');
        $writer = new Xlsx($spreadsheet);
        $writer->save('php://output');
        //删除清空：
        $spreadsheet->disconnectWorksheets();
        unset($spreadsheet);
        exit;

    }

    /**
     * 处理多sheet
     * @author bwy <xxx@xxx.com>
     * @param [type] $spreadsheet
     * @param [type] $n
     * @param [type] $data
     * @return void
     */
    public  function opSheet($spreadsheet,$n, $data)
    {
        //$spreadsheet = new Spreadsheet();//-----------------------------临时



        $spreadsheet->createSheet();//创建sheet
        $objActSheet = $spreadsheet->setActiveSheetIndex($n);//设置当前的活动sheet

        $sheet = $spreadsheet->getActiveSheet($n)->setTitle("sheet1");
        $spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setBold(true)->setSize(20);
        $spreadsheet->setActiveSheetIndex($n)->setCellValue("A1", "aaaaaaaaa");
        /*$keys = $data['rows'][0];//这是你的数据键名
        $count = count($keys);//计算你所占的列数
        $infoNum = ceil(count($data['info']) / 2);//求k-v值的所占行数
        $infoStart = $infoNum + 2 ;//下面的详细信息的开始行数
        $cellName    = array('A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z', 'AA', 'AB', 'AC', 'AD', 'AE', 'AF', 'AG', 'AH', 'AI', 'AJ', 'AK', 'AL', 'AM', 'AN', 'AO', 'AP', 'AQ', 'AR', 'AS', 'AT', 'AU', 'AV', 'AW', 'AX', 'AY', 'AZ');
        $sheet = $spreadsheet->getActiveSheet($n)->setTitle("sheet1");//设置sheet的名称
        $spreadsheet->getActiveSheet($n)->mergeCells('A1:' . $cellName[$count - 1] . '1'); //合并单元格
        $spreadsheet->getActiveSheet($n)->getStyle('A1')->getFont()->setSize(20); //设置title的字体大小
        $spreadsheet->getActiveSheet($n)->getStyle('A1')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('A')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('C')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('D')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('E')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('F')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle('G')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_CENTER); //居中
        $spreadsheet->getActiveSheet($n)->getStyle("$infoStart")->getFont()->setBold(true); //标题栏加粗
        $objActSheet->setCellValue('A1', $data['title']); //设置每个sheet中的名称title*/


        /**
         * 图中最下面的数据信息循环
         */
        /*foreach ($data['rows'] as $key => $item)
        {
            //循环设置单元格：
            //$key+$infoStart,因为第一行是表头，所以写到表格时   从第数据行开始写
            for ($i = 65; $i < $count + 65; $i++)
            {
                //数字转字母从65开始：
                //$sheet->setCellValue(strtoupper(chr($i)) . ($key + "$infoStart"), $item[[$keys][$i - 65]]);
                $sheet->setCellValue(strtoupper(chr($i)) . ($key+"$infoStart"), $item[$i - 65]);
                $spreadsheet->getActiveSheet($n)->getColumnDimension(strtoupper(chr($i)))->setWidth(20); //固定列宽
            }
        }*/

        /**
         * 处理图中的中间区 团长名称之类的
         */
        $rowNumber = 1;
        $infoIndex = 0;
        /*foreach ($data['info'] as $key => $value) {
            if ($infoIndex % 2 == 0) {
                $rowNumber++;
                $infoCellName1 = 'A' . $rowNumber;
                $infoCellMegreRange = 'B' . $rowNumber . ':C' . $rowNumber;
                $infoCellName2 = 'B' . $rowNumber;
            } else {
                $infoCellName1 = 'D' . $rowNumber;
                $infoCellMegreRange = 'E' . $rowNumber . ':F' . $rowNumber;
                $infoCellName2 = 'E' . $rowNumber;
            }
            $spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName1, $key);
            $spreadsheet->getActiveSheet($n)->mergeCells($infoCellMegreRange);
            $spreadsheet->setActiveSheetIndex($n)->setCellValue($infoCellName2, $value);
            $infoIndex++;
        }*/
    }


}